package com.citic.topview.system.util;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import javax.sql.DataSource;

public class SqlHelper {

	private final DataSource dataSource;
	private final String dbName;
	private final String tableName;
	
	public SqlHelper(DataSource dataSource, String dbName, String tableName) {
		this.dataSource = dataSource;
		this.dbName = dbName;
		this.tableName = tableName;
	}
	
	public String getDbName() {
		return dbName;
	}
	
	public DataSource getDataSource() {
		return dataSource;
	}
	
	public String getTableName() {
		return tableName;
	}
	
	public Statement getStatement() throws ClassNotFoundException, SQLException{
        Connection con = dataSource.getConnection();
        Statement statement = con.createStatement();
        return statement;
    }

	public List<String> Get(String sql, String columnName) {
        List<String> result = new ArrayList<String>();
        Statement statement = null;
        try {
            statement = getStatement();
            ResultSet set = statement.executeQuery(sql);
            while (set.next()) {
                result.add(set.getString(columnName));
            }
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                closeStatement(statement);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return result;
    }
	
	public List<HashMap<String, Object>> Get(String sql) {
		List<HashMap<String, Object>> result = new ArrayList<HashMap<String, Object>>();
        Statement statement = null;
        
        try {
        	statement = getStatement();
            ResultSet set = statement.executeQuery(sql);
            ResultSetMetaData meta = set.getMetaData();
            int columnCount = meta.getColumnCount();
            
            while (set.next()) {
                HashMap<String, Object> row = new HashMap<>();
                for (int i = 1; i <= columnCount; i++) {
                    String column = meta.getColumnName(i);
                    row.put(column, set.getObject(column));
                }
                result.add(row);
            }
		} catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                closeStatement(statement);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        
        return result;
	}
	
	public void closeStatement(Statement statement) throws SQLException {
        if (statement != null) {
            Connection con = statement.getConnection();
            statement.close();
            if (con != null) {
                con.close();
            }
        }
    }
}
